package com.handy.database.page;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
//import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.handy.database.DBUtil;

/**
 * 使用Apache dbutil工具包访问数据库. <br>
 * 默认值:<br>
 * 每页最大显示记录数为20,setMaxLine(20).<br>
 * 页面翻页数量显示最大为10,setPageListCount(10).<br>
 * 
 * 使用方法:<br>
 * 实例化或继承;<br>
 * 1.设置当前显示页 setSpecify(pageNo);<br>
 * 2.设置字段列表(不设置为全部字段);<br>
 * 3.设置表名 setTableName("table name");<br>
 * 4.设置查询条件 setFindCondition("where ?=?");<br>
 * 5.设置conditionParam,当findCondition中使用?来代替字段值时有效. 
 * 6.设置每页最大显示条数 setMaxLine(20),每页最多显示20条.<br>
 * 7.设置页列表最大值setPageListCount(10),例如当前为第3页，则显示的页列表为1 2 3 4 5 6 7 8 9 10.<br>
 * 8.设置数据库类型.<br>
 * 9.设置类类型，即读出数据后要填充的bean;<br>
 * 10.设置数据源;<br>
 * 11.设置传给页面的其它参数;pageQuery("parm1","123");<br>
 * 12.设置用于分页的字段,默认使用id分页（mysql需要使用）;<br>
 * 13.执行query().<br>
 * 14.取记录集recorders.
 * 
 * @author rocken.zeng@gmail.com
 * 
 */
public class DBUtilPage extends AbstractPage {

	/**
	 * 查找记录操作.
	 */
	@SuppressWarnings({ "unchecked", "deprecation" })
	@Override
	protected void recorders() {
		if (getDbKind() == 0){
			log.error("请设定数据库类型,如设置数据库为ORACLE数据库:setDbKind(DB_ORACLE)");
			return;
		}else if (getTCount() == 0) {
			return;
		}

		String sql = "";
		switch (getDbKind()) {
		case DBUtil.DB_KIND_ORACLE:
			if (getColumns().equals("")) {
				sql = "SELECT * FROM " + getTableName() + " "
						+ getFindCondition();
				sql = "SELECT rownum r1,t.* FROM (" + sql + ") t";
				sql = "SELECT r.* FROM (" + sql + ") r WHERE r1 BETWEEN "
						+ getOffset() + " AND " + getBelow();
			} else {
				sql = "SELECT " + getColumns() + " FROM " + getTableName()
						+ " " + getFindCondition();
				sql = "SELECT rownum r1,t.* FROM (" + sql + ") t";
				sql = "SELECT r.* FROM (" + sql + ") r WHERE r1 BETWEEN "
						+ getOffset() + " AND " + getBelow();
			}
			break;
		case DBUtil.DB_KIND_MYSQL:
			if (getColumns().equals("")) {
				sql = "SELECT * FROM " + getTableName() + " "
						+ getFindCondition() + " limit " + (getOffset() - 1)
						+ "," + getBelow();
			} else {
				sql = "SELECT " + getColumns() + " FROM " + getTableName()
						+ " " + getFindCondition();
			}
			break;
		case DBUtil.DB_KIND_SQLSERVER:
//			String minString = "";
//			String maxString = "";
//			if (getColumns().equals("")) {
//				if (getFindCondition().endsWith("desc")
//						|| getFindCondition().endsWith("DESC")) {
//					maxString = "select min(id) from " + getTableName()
//							+ " where id in (select top "
//							+ (getMaxLine() * (getCPage() - 1) + 1)
//							+ " id from " + getTableName() + " "
//							+ getFindCondition() + ")";
//					minString = "select min(id) from " + getTableName()
//							+ " where id in (select top "
//							+ (getMaxLine() * getCPage()) + " id from "
//							+ getTableName() + " " + getFindCondition() + ") ";
//				} else {
//					maxString = "select max(id) from " + getTableName()
//							+ " where id in (select top " + (20 * getCPage())
//							+ " id from " + getTableName() + " "
//							+ getFindCondition() + ")";
//					minString = "select max(id) from " + getTableName()
//							+ " where id in (select top "
//							+ (20 * (getCPage() - 1) + 1) + " id from "
//							+ getTableName() + " " + getFindCondition() + ") ";
//				}
//				if (getFindCondition().startsWith("WHERE")) {
//					if (getFindCondition().contains("ORDER")) {
//						String _temp = getFindCondition().substring(
//								getFindCondition().lastIndexOf("ORDER"));
//						sql = (getFindCondition().substring(0,
//								getFindCondition().lastIndexOf("ORDER"))
//								+ " and id >=("
//								+ minString
//								+ ") and id<=("
//								+ maxString + ") " + " " + _temp);
//					} else {
//						sql = (getFindCondition() + " and id >=(" + minString
//								+ ") and id<=(" + maxString + ") ");
//					}
//				} else {
//					sql = (" where id >=(" + minString + ") and id<=("
//							+ maxString + ")" + " " + getFindCondition());
//				}
//				sql = "SELECT * FROM " + getTableName() + " " + sql;
//			} else {
//				if (getFindCondition().endsWith("desc")
//						|| getFindCondition().endsWith("DESC")) {
//					maxString = "select min(id) from " + getTableName()
//							+ " where id in (select top "
//							+ (getMaxLine() * (getCPage() - 1) + 1)
//							+ " id from " + getTableName() + " "
//							+ getFindCondition() + ")";
//					minString = "select min(id) from " + getTableName()
//							+ " where id in (select top "
//							+ (getMaxLine() * getCPage()) + " id from "
//							+ getTableName() + " " + getFindCondition() + ") ";
//
//				} else {
//					maxString = "select max(id) from " + getTableName()
//							+ " where id in (select top " + (20 * getCPage())
//							+ " id from " + getTableName() + " "
//							+ getFindCondition() + ")";
//					minString = "select max(id) from " + getTableName()
//							+ " where id in (select top "
//							+ (20 * (getCPage() - 1) + 1) + " id from "
//							+ getTableName() + " " + getFindCondition() + ") ";
//				}
//				if (getFindCondition().startsWith("WHERE")) {
//					if (getFindCondition().contains("ORDER")) {
//						String _temp = getFindCondition().substring(
//								getFindCondition().lastIndexOf("ORDER"));
//						sql = (getFindCondition().substring(0,
//								getFindCondition().lastIndexOf("ORDER"))
//								+ " and id >=("
//								+ minString
//								+ ") and id<=("
//								+ maxString + ")" + " " + _temp);
//					} else {
//						sql = (getFindCondition() + " and id >=(" + minString
//								+ ") and id<=(" + maxString + ") ");
//					}
//				} else {
//					sql = (" where id >=(" + minString + ") and id<=("
//							+ maxString + ") " + " " + getFindCondition());
//				}
//				sql = "SELECT " + getColumns() + " FROM " + getTableName()
//						+ " " + sql;
//			}
			break;
		case DBUtil.DB_KIND_DB2:
			break;
		case DBUtil.DB_KIND_INFORMIX:
			break;
		case DBUtil.DB_KIND_SYBASE:
			break;
		}

		if (log.isDebugEnabled()) {
			log.info(sql);
		}
		querySQL = sql;

		ResultSetHandler h = null;
		if (null != getBean()){
			h = new BeanListHandler(bean);
		}else{
			//h = new ArrayListHandler();//数组组装
			h = new MapListHandler();//map组装
		}
		long start = 0;
		if (log.isDebugEnabled()) {
			start = Calendar.getInstance().getTimeInMillis();
			log.debug("start find  :" + start);
		}
		try {
			QueryRunner run = new QueryRunner(dbUtil
					.dataSource(getDataSourceName()));
			if (null == getConditionParam()) {
				setRecorders((ArrayList) run.query(sql, h));
			} else {
				setRecorders((ArrayList) run.query(sql, h, getConditionParam()));
			}

		} catch (SQLException e) {
			log.error(e.getMessage());
		}

		if (log.isDebugEnabled()) {
			long end = Calendar.getInstance().getTimeInMillis();
			log.debug("finish find :" + end);
			log.debug("耗时：" + (end - start));
		}
	}

	/**
	 * 查找数据库中符合条件的记录总数.
	 */	
	@SuppressWarnings({ "unchecked", "deprecation" })
	@Override
	protected void totalRecorder() {

		String sql = "SELECT count(*) FROM " + getTableName() + " "
				+ getFindCondition();

		if (log.isDebugEnabled()) {
			log.debug(sql);
		}
		try {
			QueryRunner run = new QueryRunner(dbUtil
					.dataSource(getDataSourceName()));
			if (null == getConditionParam()) {
				ArrayList _cols = (ArrayList) run.query(sql,
						new ColumnListHandler());
				if (log.isDebugEnabled()) {
					log.debug(_cols);
				}
				if (_cols != null && !_cols.isEmpty()) {
					setTCount(Integer.parseInt(String.valueOf(_cols.iterator().next())));
				}else{
					setTCount(0);
				}
			} else {
				ArrayList _cols = (ArrayList) run.query(sql,
						 new ColumnListHandler(),getConditionParam());
				if (_cols != null && !_cols.isEmpty()) {
					setTCount(Integer.parseInt(String.valueOf(_cols.iterator().next())));
				}else{
					setTCount(0);
				}
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}

	}

	private static final Log log = LogFactory.getLog(DBUtilPage.class);
	
	private DBUtil dbUtil = DBUtil.getInstance();

	private String dataSourceName = null;// 数据库

	private Object[] conditionParam;// 当findCondition中使用?来代替字段值时有效.

	private String conditionColumn = "id";// 用于分页的字段,默认使用id分页.

	@SuppressWarnings("unchecked")
	private Class bean = null;

	public String getDataSourceName() {
		return dataSourceName;
	}

	public void setDataSourceName(String dataSourceName) {
		this.dataSourceName = dataSourceName;
	}

	public Object[] getConditionParam() {
		return conditionParam;
	}

	public void setConditionParam(Object[] conditionParam) {
		this.conditionParam = conditionParam;
	}

	public String getConditionColumn() {
		return conditionColumn;
	}

	public void setConditionColumn(String conditionColumn) {
		this.conditionColumn = conditionColumn;
	}

	@SuppressWarnings("unchecked")
	public Class getBean() {
		return bean;
	}

	@SuppressWarnings("unchecked")
	public void setBean(Class bean) {
		this.bean = bean;
	}
}